package ee.maoinvest.backend.model.dao.postgresqlimplementations;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import ee.maoinvest.backend.database.ConnectionManager;
import ee.maoinvest.backend.model.dao.LatestDatesDAO;

public class LatestDatesDAOPostgresqlImpl implements LatestDatesDAO
{
	Logger logger = Logger.getLogger(LatestDatesDAOPostgresqlImpl.class);
	Connection dbConnection;
	String sql;
	PreparedStatement preparedStatement;
	Statement statement;
	ResultSet resultSet;
	private final int WARNING_NO_ROWS_AFFECTED = 0;
	private int rowsAffected;
	
	public LatestDatesDAOPostgresqlImpl(Connection dbConnection)
	{
		this.dbConnection = dbConnection;
	}

	@Override
	public Date getLatestDateWhileInsertingNewContractBetweenEnterpriseAndElectricityMeter(
			int electricityContractID, int enterpriseID, int electricityMeterID)
	{
		Date dateRetrieved = null;
		sql = "SELECT max(dateReadingsWereTaken) AS latestDate FROM " + 
			"((SELECT dateReadingsWereTaken FROM Electricity_meter_readings WHERE electricityMeterFK = ? " + 
			"AND electricityMeterReadingID IN( " + 
			"SELECT max(electricityMeterReadingID) FROM Electricity_meter_readings WHERE electricityMeterFK = ?)) " + 
			"UNION " + 
			"SELECT dateInserted FROM Electricity_contracts WHERE electricityContractID = ? " + 
			"UNION " + 
			"SELECT dateInserted FROM Enterprises WHERE enterpriseID = ?) AS LatestDate";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, electricityMeterID);
			preparedStatement.setInt(2, electricityMeterID);
			preparedStatement.setInt(3, electricityContractID);
			preparedStatement.setInt(4, enterpriseID);
			
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				dateRetrieved = new Date(System.currentTimeMillis());
				dateRetrieved = resultSet.getDate("latestDate");
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return dateRetrieved;
	}

	@Override
	public Date getLatestDateWhileInsertingSubstationMainElectricityMeter(
			int substationID, int transformerCoefficentStandardID) 
	{
		Date dateRetrieved = null;
		sql = "SELECT max(dateInserted) AS latestDate FROM " + 
				"(SELECT dateInserted FROM Substations WHERE substationID = ? " +
				"UNION " + 
				"SELECT dateInserted FROM Transformer_coefficent_standards WHERE transformerCoefficentStandardID = ?) AS LatestDate";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, substationID);
			preparedStatement.setInt(2, transformerCoefficentStandardID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				dateRetrieved = new Date(System.currentTimeMillis());
				dateRetrieved = resultSet.getDate("latestDate");
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return dateRetrieved;
	}

	@Override
	public Date getLatestDateWhileInsertingLocalTerritoryElectricityMeter(
			int parentElectricityMeterID, int transformerCoefficentStandardID) 
	{
		Date dateRetrieved = null;
		sql = "SELECT max(dateReadingsWereTaken) AS latestDate FROM " +
				"((SELECT dateReadingsWereTaken FROM Electricity_meter_readings WHERE electricityMeterFK = ? " +
				"AND electricityMeterReadingID IN( " +
				"SELECT max(electricityMeterReadingID) FROM Electricity_meter_readings WHERE electricityMeterFK = ?)) " +
				"UNION " +
				"SELECT dateInserted FROM Transformer_coefficent_standards WHERE transformerCoefficentStandardID = ?) AS LatestDate";
		
		try
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, parentElectricityMeterID);
			preparedStatement.setInt(2, parentElectricityMeterID);
			preparedStatement.setInt(3, transformerCoefficentStandardID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				dateRetrieved = new Date(System.currentTimeMillis());
				dateRetrieved = resultSet.getDate("latestDate");
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return dateRetrieved;
	}
	
	@Override
	public Date getLatestDateWhileInsertingOuterTerritoryElectricityMeter(
			int electricityContractID, int transformerCoefficentStandardID) 
	{
		Date dateRetrieved = null;
		sql = "SELECT max(dateInserted) AS latestDate FROM " + 
				"(SELECT dateInserted FROM Electricity_contracts WHERE electricityContractID = ? " +
				"UNION " + 
				"SELECT dateInserted FROM Transformer_coefficent_standards WHERE transformerCoefficentStandardID = ?) AS LatestDate";
		
		try
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, electricityContractID);
			preparedStatement.setInt(2, transformerCoefficentStandardID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				dateRetrieved = new Date(System.currentTimeMillis());
				dateRetrieved = resultSet.getDate("latestDate");
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return dateRetrieved;
	}
	
	@Override
	public Date getLatestDateWhileDeletingElectricityMeterParentMeterIncluded(
			int electricityMeterIDChosenToDelete,
			int parentMeterIDOfMeterChosenToDelete) 
	{
		Date dateRetrieved = null;
		sql = "SELECT max(dateReadingsWereTaken) AS latestDate FROM " + 
				"((SELECT dateReadingsWereTaken FROM Electricity_meter_readings WHERE electricityMeterFK = ? " + 
				"AND electricityMeterReadingID IN( " + 
				"SELECT max(electricityMeterReadingID) FROM Electricity_meter_readings WHERE electricityMeterFK = ?)) " + 
				"UNION " + 
				"(SELECT dateReadingsWereTaken FROM Electricity_meter_readings WHERE electricityMeterFK = ? " + 
				"AND electricityMeterReadingID IN( " + 
				"SELECT max(electricityMeterReadingID) FROM Electricity_meter_readings WHERE electricityMeterFK = ?))) AS LatestDate";
		
		try
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, electricityMeterIDChosenToDelete);
			preparedStatement.setInt(2, electricityMeterIDChosenToDelete);
			preparedStatement.setInt(3, parentMeterIDOfMeterChosenToDelete);
			preparedStatement.setInt(4, parentMeterIDOfMeterChosenToDelete);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				dateRetrieved = new Date(System.currentTimeMillis());
				dateRetrieved = resultSet.getDate("latestDate");
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return dateRetrieved;
	}
	
	@Override
	public Date getLatestDateWhileDeletingElectricityMeter(
			int electricityMeterIDChosenToDelete) 
	{
		Date dateRetrieved = null;
		sql = "SELECT max(dateReadingsWereTaken) AS latestDate FROM " +
				"(SELECT dateReadingsWereTaken FROM Electricity_meter_readings WHERE electricityMeterFK = ? " +
				"AND electricityMeterReadingID IN( " +
				"SELECT max(electricityMeterReadingID) FROM Electricity_meter_readings WHERE electricityMeterFK = ?)) AS LatestDate";
		
		try
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, electricityMeterIDChosenToDelete);
			preparedStatement.setInt(2, electricityMeterIDChosenToDelete);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				dateRetrieved = new Date(System.currentTimeMillis());
				dateRetrieved = resultSet.getDate("latestDate");
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return dateRetrieved;
	}
	
	@Override
	public Date getLatestDateWhileChangingLocalTerritoryElectricityMeterParentMeter(
			int electricityMeterID, int parentMeterIDOfUnwiredChild,
			int parentElectricityMeterIDThatReplacesTheOldOne) 
	{
		Date dateRetrieved = null;
		sql = "SELECT max(dateReadingsWereTaken) AS latestDate FROM " +
				"((SELECT dateReadingsWereTaken FROM Electricity_meter_readings WHERE electricityMeterFK = ? " + 
				"AND electricityMeterReadingID IN( " + 
				"SELECT max(electricityMeterReadingID) FROM Electricity_meter_readings WHERE electricityMeterFK = ?)) " + 
				"UNION " + 
				"(SELECT dateReadingsWereTaken FROM Electricity_meter_readings WHERE electricityMeterFK = ? " + 
				"AND electricityMeterReadingID IN( " + 
				"SELECT max(electricityMeterReadingID) FROM Electricity_meter_readings WHERE electricityMeterFK = ?)) " + 
				"UNION " + 
				"(SELECT dateReadingsWereTaken FROM Electricity_meter_readings WHERE electricityMeterFK = ? " + 
				"AND electricityMeterReadingID IN( " + 
				"SELECT max(electricityMeterReadingID) FROM Electricity_meter_readings WHERE electricityMeterFK = ?))) AS LatestReading";
		
		try
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, electricityMeterID);
			preparedStatement.setInt(2, electricityMeterID);
			preparedStatement.setInt(3, parentMeterIDOfUnwiredChild);
			preparedStatement.setInt(4, parentMeterIDOfUnwiredChild);
			preparedStatement.setInt(5, parentElectricityMeterIDThatReplacesTheOldOne);
			preparedStatement.setInt(6, parentElectricityMeterIDThatReplacesTheOldOne);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				dateRetrieved = new Date(System.currentTimeMillis());
				dateRetrieved = resultSet.getDate("latestDate");
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return dateRetrieved;
	}
	
	@Override
	public Date getLatestDateOfSubstationMainElectricityMeterToBeReplaced(
			int electricityMeterID) 
	{
		return getElectricityMeterLatestReadingDate(electricityMeterID);
	}
	
	@Override
	public Date getElectricityMeterLatestReadingDate(int electricityMeterID)
	{
		Date dateRetrieved = null;
		sql = "SELECT max(dateReadingsWereTaken) AS latestDate FROM " +
				"(SELECT dateReadingsWereTaken FROM Electricity_meter_readings WHERE electricityMeterFK = ? " +
				"AND electricityMeterReadingID IN( " +
				"SELECT max(electricityMeterReadingID) FROM Electricity_meter_readings WHERE electricityMeterFK = ?)) AS LatestDate";
		
		try
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, electricityMeterID);
			preparedStatement.setInt(2, electricityMeterID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				dateRetrieved = new Date(System.currentTimeMillis());
				dateRetrieved = resultSet.getDate("latestDate");
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return dateRetrieved;
	}
}
